<?php

require_once('Vega/Dao/Table.php');

class Mobitv_Admin_ChannelDao extends Vega_Dao_Table {

    public function testRunM() {
        $this->_db->query('#abc' . "\n" . 'SET @x=1;');
        $this->_db->query('SELECT id FROM channel where false');
        $this->_db->query('SELECT @x');
        //$this->_db->query('SELECT id, name FROM channel where false');
        $rows = $this->_db->fetchRowSet();
        return $rows;
    }

    public function runSQL($sql) {
        return $this->_db->queryWrite($sql);
    }

    public function loadChannelId() {
        $arr = array();
        $sql = 'SELECT id, name FROM channel';
        $this->_db->query($sql);
        while ($row = $this->_db->fetchRow()) {
            $name = strtoupper($row['name']);
            $arr[$name] = $row['id'];
        }
        return $arr;
    }

    public function getDetail($channelId) {
        $channelId = intval($channelId);

        $sql = "SELECT * FROM channel WHERE id=$channelId";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function getActionListFromModule($moduleName) {
        $moduleName = $this->_db->valueQuote($moduleName);
        $sql = "SELECT * FROM admin_action WHERE module=$moduleName";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getListAll() {
        $sql = "SELECT id, name FROM channel";
        $this->_db->query($sql);
        return $this->_db->fetchRowSet();
    }

    public function getAddonList() {
        $sql = "SELECT id, name FROM channel WHERE type=" . ADDON_CHANNEL;
        $this->_db->query($sql);
        return $this->_db->fetchRowSet();
    }

    public function getList($limit, $page, $type) {
        $type = intval($type);
        $offset = $limit * ($page - 1);
        $_limit = ' LIMIT ' . $limit;
        $_offset = ' OFFSET ' . $offset;

        $sql = "SELECT c.*,
                   cc.name AS category_name,
                   a.name AS cp_name,
                   a.fullname AS cp_fullname
            FROM channel c
                LEFT JOIN channel_category cc ON c.category_id = cc.id
                LEFT JOIN admin a ON c.cp_id=a.id " .
                ($type ? "WHERE c.type=$type " : "") .
                "ORDER BY `order` desc $_limit $_offset";

        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function countAll($type) {
        $type = intval($type);
        $sql = "SELECT COUNT(channel.id) as numrow FROM channel " . ($type ? "WHERE type=$type " : "");

        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        return $row['numrow'];
    }

    public function getModuleList() {
        $sql = "SELECT DISTINCT(module) AS name FROM admin_action";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function insert($data) {
        // Get max channel's order
        $sql = "SELECT MAX(`order`) as max_order FROM channel";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $order = $row['max_order'] + 1;

        $name = $this->_db->valueQuote($data['name']);
        $friendly_name = $this->_db->valueQuote($data['friendly_name']);
        $description = $this->_db->valueQuote($data['description']);
        $server = $this->_db->valueQuote($data['server']);

        $url = $this->_db->valueQuote($data['url']);
        $iphone_url = $this->_db->valueQuote($data['iphone_url']);
        $_3gp_url = $this->_db->valueQuote($data['_3gp_url']);
        $mpeg4_url = $this->_db->valueQuote($data['mpeg4_url']);
        $h264hq_url = $this->_db->valueQuote($data['h264hq_url']);
        $bb_url = $this->_db->valueQuote($data['bb_url']);
        $wp_url = $this->_db->valueQuote($data['wp_url']);

        $hcm_url = $this->_db->valueQuote($data['hcm_url']);
        $hcm_iphone_url = $this->_db->valueQuote($data['hcm_iphone_url']);
        $hcm_3gp_url = $this->_db->valueQuote($data['hcm_3gp_url']);
        $hcm_mpeg4_url = $this->_db->valueQuote($data['hcm_mpeg4_url']);
        $hcm_h264hq_url = $this->_db->valueQuote($data['hcm_h264hq_url']);
        $hcm_bb_url = $this->_db->valueQuote($data['hcm_bb_url']);
        $hcm_wp_url = $this->_db->valueQuote($data['hcm_wp_url']);

        $url_multi = $this->_db->valueQuote($data['url_multi']);
        $url_multi_extend = $this->_db->valueQuote($data['url_multi_extend']);

        $category_id = intval($data['category_id']);
        $price = intval($data['price']);
        $cpId = intval($data['cp_id']);
        $is_hot = intval($data['is_hot']);
        $visible = intval($data['visible']);
        $type = intval($data['type']);

        $sql = "INSERT INTO channel
                SET name=$name,
                    friendly_name=$friendly_name,
                    description=$description,
                    visible=$visible,
                    is_hot=$is_hot,
                    server=$server,
                    url=$url,
                    iphone_url=$iphone_url,
                    `3gp_url`=$_3gp_url,
                    mpeg4_url=$mpeg4_url,
                    h264hq_url=$h264hq_url,
                    bb_url=$bb_url,
                    wp_url=$wp_url,
                    hcm_url=$hcm_url,
                    hcm_iphone_url=$hcm_iphone_url,
                    hcm_3gp_url=$hcm_3gp_url,
                    hcm_mpeg4_url=$hcm_mpeg4_url,
                    hcm_h264hq_url=$hcm_h264hq_url,
                    hcm_bb_url=$hcm_bb_url,
                    hcm_wp_url=$hcm_wp_url,
                    url_multi=$url_multi,
                    url_multi_extend=$url_multi_extend,
                    price=$price,
                    cp_id=$cpId,
                    `type`=$type,
                    category_id=$category_id, 
                    `order`=$order,
                    created_by=" . $_SESSION['userid'] . ",
                    created_time=now(),
                    updated_by=" . $_SESSION['userid'] . ",
                    updated_time=now()";

        return $this->_db->queryWrite($sql);
    }

    public function getLastInsertId() {
        return $this->_db->getLastInsertId();
    }

    public function save($channelId, $data) {
        $channelId = intval($channelId);
        $name = $this->_db->valueQuote($data['name']);
        $friendly_name = $this->_db->valueQuote($data['friendly_name']);
        $description = $this->_db->valueQuote($data['description']);
        $server = $this->_db->valueQuote($data['server']);

        $url = $this->_db->valueQuote($data['url']);
        $iphone_url = $this->_db->valueQuote($data['iphone_url']);
        $_3gp_url = $this->_db->valueQuote($data['_3gp_url']);
        $mpeg4_url = $this->_db->valueQuote($data['mpeg4_url']);
        $h264hq_url = $this->_db->valueQuote($data['h264hq_url']);
        $bb_url = $this->_db->valueQuote($data['bb_url']);
        $wp_url = $this->_db->valueQuote($data['wp_url']);

        $hcm_url = $this->_db->valueQuote($data['hcm_url']);
        $hcm_iphone_url = $this->_db->valueQuote($data['hcm_iphone_url']);
        $hcm_3gp_url = $this->_db->valueQuote($data['hcm_3gp_url']);
        $hcm_mpeg4_url = $this->_db->valueQuote($data['hcm_mpeg4_url']);
        $hcm_h264hq_url = $this->_db->valueQuote($data['hcm_h264hq_url']);
        $hcm_bb_url = $this->_db->valueQuote($data['hcm_bb_url']);
        $hcm_wp_url = $this->_db->valueQuote($data['hcm_wp_url']);

        $url_multi = $this->_db->valueQuote($data['url_multi']);
        $url_multi_extend = $this->_db->valueQuote($data['url_multi_extend']);

        $category_id = intval($data['category_id']);
        $price = intval($data['price']);
        $cpId = intval($data['cp_id']);
        $is_hot = intval($data['is_hot']);
        $visible = intval($data['visible']);
        $type = intval($data['type']);

        $sql = "UPDATE channel
                SET name=$name,
                    friendly_name=$friendly_name,
                    description=$description,
                    visible=$visible,
                    is_hot=$is_hot,
                    server=$server,
                    price=$price,
                    cp_id=$cpId,
                    `type`=$type,
                    url=$url,
                    iphone_url=$iphone_url,
                    `3gp_url`=$_3gp_url,
                    mpeg4_url=$mpeg4_url,
                    h264hq_url=$h264hq_url,
                    bb_url=$bb_url,
                    wp_url=$wp_url,
                    hcm_url=$hcm_url,
                    hcm_iphone_url=$hcm_iphone_url,
                    hcm_3gp_url=$hcm_3gp_url,
                    hcm_mpeg4_url=$hcm_mpeg4_url,
                    hcm_h264hq_url=$hcm_h264hq_url,
                    hcm_bb_url=$hcm_bb_url,
                    hcm_wp_url=$hcm_wp_url,
                    url_multi=$url_multi,
                    url_multi_extend=$url_multi_extend,
                    category_id=$category_id,
                    updated_by=" . $_SESSION['userid'] . ",
                    updated_time=now()
                WHERE id=$channelId";
        $result = $this->_db->queryWrite($sql);
        if($result && $visible == 0){
            $update = "UPDATE user_channel SET deleted = 1 WHERE channel_id = $channelId";
            $this->_db->queryWrite($update);
        }
        return $result;
    }

    public function updateThumbnail($channelId) {
        $sql = "UPDATE channel SET thumbnail_version=thumbnail_version + 1
                WHERE id = " . $channelId;
        return $this->_db->queryWrite($sql);
    }

    public function updateChannelCatThumbnail($channelcatId) {
        $sql = "UPDATE channel_category SET thumbnail_version=thumbnail_version + 1
                WHERE id = " . $channelcatId;
        return $this->_db->queryWrite($sql);
    }

    public function deleteFromList($list) {
        $list = "(" . implode(",", $list) . ")";
        $sql = "DELETE FROM channel WHERE id IN $list";
        $result = $this->_db->queryWrite($sql);
        if($result){
            $sql = "DELETE FROM user_channel WHERE channel_id IN $list";
            $this->_db->queryWrite($sql);
        }
        // Reorder channel
        $this->reorder();

        return $result;
    }

    /*     * *******************
     * Channel Package
     * ****************** */

    // Lay danh sach cac package ma channel thuoc
    public function getPackage($channelId) {
        $channelId = intval($channelId);
        $sql = "SELECT package_id FROM package_channel WHERE channel_id=$channelId";
        $this->_db->query($sql);
        return $this->_db->fetchRowSet();
    }

    public function addToPackage($channelId, $package_list) {
        if ($package_list) {
            $values = array();
            foreach ($package_list as $package) {
                $values[] = '(' . $package . ', ' . $channelId . ')';
            }
            $values = implode(", ", $values);
            $sql = "INSERT INTO package_channel (package_id, channel_id) VALUES $values";

            return $this->_db->queryWrite($sql);
        }
        return true;
    }

    public function updateToPackage($channelId, $package_list) {
        // Xoa
        $sql = "DELETE FROM package_channel WHERE channel_id=$channelId";
        $this->_db->queryWrite($sql);
        // Ghi package cap nhat
        return $this->addToPackage($channelId, $package_list);
    }

    /*     * *******************
     * Channel Category
     * ****************** */

    public function getCategoryDetail($categoryId) {
        $categoryId = intval($categoryId);

        $sql = "SELECT * FROM channel_category WHERE id=$categoryId";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function getCategoryList() {
        $sql = "SELECT * FROM channel_category";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function insertCategory($data) {
        $name = $this->_db->valueQuote($data['name']);

        $sql = "INSERT INTO channel_category SET name=$name, updated_by=" . $_SESSION['userid'] . ", updated_time=now()";

        return $this->_db->queryWrite($sql);
    }

    public function saveCategory($categoryId, $data) {
        $categoryId = intval($categoryId);
        $name = $this->_db->valueQuote($data['name']);

        $sql = "UPDATE channel_category SET name=$name, updated_by=" . $_SESSION['userid'] . ", updated_time=now() WHERE id=$categoryId";

        return $this->_db->queryWrite($sql);
    }

    public function deleteCategoryFromList($list) {
        $list = "(" . implode(",", $list) . ")";
        $sql = "DELETE FROM channel_category WHERE id IN $list";
        return $this->_db->queryWrite($sql);
    }

    /*
     * Hàm thay đổi (di chuyển) thứ tự hiển thị của một video chọn lọc
     *
     */

    public function move($channelId, $curOrder, $newOrder, $maxOrder) {
        // Chuyen len dau danh sach
        if ($newOrder == 1) {
            $sql = "UPDATE channel SET `order`=`order` + 1 WHERE `order` < $curOrder";
            $this->_db->queryWrite($sql);

            $sql = "UPDATE channel SET `order`=$newOrder WHERE id = $channelId";
            return $this->_db->queryWrite($sql);
        } elseif ($newOrder == $maxOrder) {
            $sql = "UPDATE channel SET `order`=`order` - 1 WHERE `order` > $curOrder";
            $this->_db->queryWrite($sql);

            $sql = "UPDATE channel SET `order`=$newOrder WHERE id = $channelId";
            return $this->_db->queryWrite($sql);
        } elseif ($newOrder == $curOrder + 1 ||
                $newOrder == $curOrder - 1
        ) {//chuyển xuống dưới | lên trên
            $sql = "UPDATE channel SET `order`=$curOrder WHERE `order`=$newOrder";
            $this->_db->queryWrite($sql);

            $sql = "UPDATE channel SET `order`=$newOrder WHERE id=$channelId";
            return $this->_db->queryWrite($sql);
        } else {
            return true;
        }
    }

    /*
     * Hàm sắp xếp lại thứ tự hiển thị kênh
     * Hàm liên quan: move
     */

    public function reorder() {
        $sql = "SELECT id FROM channel ORDER BY `order` ASC";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowSet();
        $index = 1;
        $ret = true;
        foreach ($rows as $row) {
            $id = $row['id'];
            $sql = "UPDATE channel SET `order`=$index WHERE id=$id";
            if (!($this->_db->queryWrite($sql)))
                $ret = false;
            $index++;
        }
        return $ret;
    }

}

?>